package com.jpa.web.service.impl;

import com.jpa.web.model.Student;
import com.jpa.web.service.StudentDao;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by BKWJA on 2018/10/25.
 * stduentDao访问接口实现类：通过spring jdbc 的方式操作
 */
public class StudentDaoSpringJdbcImpl implements StudentDao {

    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Student> queryList() {
        final List<Student> studentList = new ArrayList<Student>();
        String sql = "SELECT * from student";
        //查询使用query
        jdbcTemplate.query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Student stu = new Student();
                stu.setId( rs.getInt("id"));
                stu.setUsername(rs.getString("username"));
                stu.setAge(rs.getInt("age"));
                stu.setSex(rs.getString("sex"));
                stu.setCreateTime(rs.getString("createTime"));
                stu.setUpdateTime(rs.getString("updateTime"));

                studentList.add(stu);
            }
        });

        return studentList;
    }

    @Override
    public void saveStu(Student student) {

        String sql = "INSERT INTO student(username,age,sex,createTime,updateTime) VALUES (?,?,?,?,?)";
        //插入，更新，删除使用update
        jdbcTemplate.update(sql,new Object[]{
                student.getUsername(),student.getAge(),student.getSex(),
                student.getCreateTime(),student.getUpdateTime()});

    }

    @Override
    public void deleteStu(int id) {
        String sql = "DELETE FROM student where id = ?";
        jdbcTemplate.update(sql,id);
    }

    @Override
    public int updateStu(Student student) {
        String sql = "UPDATE student set username = ?,age = ?,sex = ?,updateTime = ? WHERE id = ?";
        jdbcTemplate.update(sql,new Object[]{
                student.getUsername(),student.getAge(),student.getSex(),student.getUpdateTime(),student.getId()
        });
        return 1;
    }

    @Override
    public Student seletStuById(int id) {
        final List<Student> studentList = new ArrayList<Student>();
        String sql = "SELECT * from student WHERE id = ?";
        jdbcTemplate.query(sql,new Object[]{id}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Student stu = new Student();
                stu.setId( rs.getInt("id"));
                stu.setUsername(rs.getString("username"));
                stu.setAge(rs.getInt("age"));
                stu.setSex(rs.getString("sex"));
                stu.setCreateTime(rs.getString("createTime"));
                stu.setUpdateTime(rs.getString("updateTime"));
                studentList.add(stu);
            }
        });
        if (studentList.size()>0 && studentList != null){
            return studentList.get(0);
        }
        return null;
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
}
